package com.edu.app.mapper.linkmappers;

import com.edu.app.bean.core.SysActivity;
import com.edu.app.bean.core.SysCustomer;
import com.edu.app.bean.linkbeans.SysCustomerActivity;
import com.edu.app.bean.virtualbean.CustomerActivities;
import com.edu.app.util.AppBaseMapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface SysCustomerActivityMapper extends AppBaseMapper<SysCustomerActivity> {
    @Select("SELECT t3.* FROM S_CUSTOMER t1 INNER JOIN S_CUSTOMER_ACTIVITY t2 on t1.ID = t2.CUSTOMER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.id = #{id}")
    List<SysActivity> getActivityByCustomerId(Integer id);

    @Select("SELECT t1.* FROM S_CUSTOMER t1 INNER JOIN S_CUSTOMER_ACTIVITY t2 on t1.ID = t2.CUSTOMER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t3.id = #{id}")
    List<SysCustomer> getCustomerByActivityId(Integer id);

    //注：传参数到Mapper层，当参数超过两个以上的时候，注意使用@Param标注
    @Select("SELECT * FROM S_CUSTOMER_ACTIVITY WHERE customer_id = #{customer_id} and activity_id = #{activity_id}")
    SysCustomerActivity getCustomerActivity(@Param("customer_id") Integer customer_id, @Param("activity_id") Integer activity_id);

    @Select("SELECT t3.* FROM S_CUSTOMER t1 INNER JOIN S_CUSTOMER_ACTIVITY t2 on t1.ID = t2.CUSTOMER_ID INNER " +
            "JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE t1.id = #{id} ORDER BY t3.time DESC")
    List<SysActivity> getCustomerActivityByPage(Integer id);

    @Select("SELECT DISTINCT t3.id, t4.value as activity_type, t3.time FROM S_CUSTOMER t1 " +
            "INNER JOIN S_CUSTOMER_ACTIVITY t2 on t1.ID = t2.CUSTOMER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN S_LIST_OF_VALUE t4 on t3.type_id = t4.id " +
            "WHERE t1.id = #{id} and t4.type like #{type} and t3.status != '3' and t3.time < now() ORDER BY t3.time DESC")
    List<CustomerActivities> getCustomerActivities(@Param("id") Integer id, @Param("type") String type);

    @Select("SELECT DISTINCT t3.id, t4.value as activity_type, t3.time FROM S_CUSTOMER t1 " +
            "INNER JOIN S_CUSTOMER_ACTIVITY t2 on t1.ID = t2.CUSTOMER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN S_LIST_OF_VALUE t4 on t3.type_id = t4.id " +
            "WHERE t1.id = #{id} and t3.status != '3' and t3.time < now() ORDER BY t3.time DESC")
    List<CustomerActivities> getCustomerActivitiesAll(@Param("id") Integer id);

    @Select("DELETE FROM S_CUSTOMER_ACTIVITY WHERE activity_id = #{activity_id}")
    Integer deleteCustomerActivityByActivityId(Integer activity_id);

    @Select("DELETE FROM S_CUSTOMER_ACTIVITY WHERE customer_id = #{customer_id}")
    Integer deleteCustomerActivityByCustomerId(Integer customer_id);
}
